package vn.com.pavibook.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import vn.com.pavibook.dao.TransactionDao;
import vn.com.pavibook.domain.model.PagingInfo;
import vn.com.pavibook.domain.model.SearchResult;
import vn.com.pavibook.model.vo.TransactionVO;
import vn.com.pavibook.utils.DaoHelper;
import vn.com.pavibook.utils.DataHelper;

@Repository
public class TransactionDaoImpl extends DaoHelper<TransactionVO> implements TransactionDao{
	
	private final Long LONG_TIME_A_DAY = (long) 86400000;
	
	@SuppressWarnings("rawtypes")
	class TransactionVoRowMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			TransactionVO transactionVO = new TransactionVO();
			transactionVO.setId(rs.getInt("id"));
			transactionVO.setBookId(rs.getInt("BookId"));
			transactionVO.setCustomerId(rs.getInt("CustomerId"));
			transactionVO.setTotalPrice(rs.getDouble("TotalPrice"));
			transactionVO.setTransactionDate(rs.getTimestamp("TransactionDate"));
			transactionVO.setBookName(rs.getString("BookName"));
			transactionVO.setCustormerFullName(rs.getString("firstname") + " " + rs.getString("middle") + " " + rs.getString("lastname"));
			transactionVO.setPublisherId(rs.getInt("PublisherId"));
			transactionVO.setPublisherName(rs.getString("PublisherName"));
			transactionVO.setAuthor(rs.getString("Author"));
			return transactionVO;
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<TransactionVO> getTransection(PagingInfo pagingInfo,
			String keyword, String typeStatistic, int publisherId, String role, String typeSearch) {
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT t.id id, t.book_id BookId, t.customer_id CustomerId, t.transaction_date TransactionDate, ");
		sql.append("t.total_price TotalPrice, b.name BookName,b.author Author, p.id PublisherId, p.name PublisherName, c.firstname, c.lastname, c.middle ");
		sql.append("FROM transaction t, book b, publisher p, customer c ");
		sql.append("WHERE t.customer_id = c.id ");
		sql.append("AND t.book_id = b.id ");
		sql.append("AND b.publisher_id = p.id ");
		
		if(DataHelper.UserRole.ROLE_PUBLISHER.getCode().equals(role)){
			sql.append("AND b.publisher_id = " + publisherId + " ");
		}
		if(DataHelper.TypeStatistic.TYPE_STATISTIC_TODAY.getCode().equals(typeStatistic)){
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			Date today = new Date();
			Date tomorrow = new Date(today.getTime() + LONG_TIME_A_DAY);
			String todayString = dateFormat.format(today);
			String tomorrowString = dateFormat.format(tomorrow);
			
			sql.append("AND t.transaction_date between '" + todayString + "' AND '" + tomorrowString + "' ");
		}
		
		if(StringUtils.isNotEmpty(typeSearch) && StringUtils.isNotEmpty(keyword)){
			String typeSearchKey = getTypeSearchKey(typeSearch);
			sql.append("AND (");
			String[] keyStrings = keyword.split(" ");
			if(DataHelper.TransactionTypeSearch.TRANSACTION_TYPE_SEARCH_CUSTOMER.getCode().equals(typeSearch)){
				for (int i = 0; i < keyStrings.length; i++) {
					String s = "'%" + keyStrings[i] + "%'";
					if (i == 0) {
						sql.append(" c.firstname LIKE " + s + " OR c.middle LIKE " + s + " OR c.lastname LIKE " + s);
					} else {
						sql.append(" OR c.firstname LIKE " + s + " OR c.middle LIKE " + s + " OR c.lastname LIKE " + s);
					}
				}
				sql.append(")");
			}else{
				for (int i = 0; i < keyStrings.length; i++) {
					String s = "'%" + keyStrings[i] + "%'";
					if (i == 0) {
						sql.append(" " + typeSearchKey + " LIKE " + s );
					} else {
						sql.append(" OR " + typeSearchKey + " LIKE " + s );
					}
				}
				sql.append(") ");
			}
		} else if(StringUtils.isEmpty(typeSearch) && StringUtils.isNotEmpty(keyword)){
			sql.append("AND (");
			String[] keyStrings = keyword.split(" ");
			for (int i = 0; i < keyStrings.length; i++) {
				String s = "'%" + keyStrings[i] + "%'";
				if (i == 0) {
					sql.append(" c.firstname LIKE ").append(s).append(" OR " );
					sql.append(" c.lastname LIKE ").append(s).append(" OR " );
					sql.append(" c.middle LIKE ").append(s).append(" OR " );
					sql.append(" b.name LIKE ").append(s).append(" OR " );
					sql.append(" p.name LIKE ").append(s).append(" OR " );
					sql.append(" b.author LIKE ").append(s);
				} else {
					sql.append(" OR ");
					sql.append(" c.firstname LIKE ").append(s).append(" OR " );
					sql.append(" c.lastname LIKE ").append(s).append(" OR " );
					sql.append(" c.middle LIKE ").append(s).append(" OR " );
					sql.append(" b.name LIKE ").append(s).append(" OR " );
					sql.append(" p.name LIKE ").append(s).append(" OR " );
					sql.append(" b.author LIKE ").append(s);
				}
			}
			sql.append(") ");
		} 
		sql.append("ORDER BY t.transaction_date");
		SearchResult<TransactionVO> searchResult = queryPaging(sql.toString(), null, new TransactionVoRowMapper(),
				pagingInfo);
		return searchResult;
	}
	
	private String getTypeSearchKey(String typeSearch){
		String typeSearchKey = StringUtils.EMPTY;
		if(StringUtils.isNotEmpty(typeSearch)){
			if(typeSearch.equals(DataHelper.TransactionTypeSearch.TRANSACTION_TYPE_SEARCH_BOOK.getCode())){
				typeSearchKey = "b.name";
			}else if(typeSearch.equals(DataHelper.TransactionTypeSearch.TRANSACTION_TYPE_SEARCH_AUTHOR.getCode())){
				typeSearchKey = "b.author";
			}else if(typeSearch.equals(DataHelper.TransactionTypeSearch.TRANSACTION_TYPE_SEARCH_PUBLISHER.getCode())){
				typeSearchKey = "p.name";
			}
		}
		return typeSearchKey;
	}
	@Override
	public void saveTransaction(TransactionVO transactionVO) {
		String sql= "Insert Into transaction (id,customer_id,book_id,transaction_date,total_price, transaction_type) Value (?,?,?,?,?, ?)";
		Object[] params = new Object[] {transactionVO.getId(),transactionVO.getCustomerId(),transactionVO.getBookId(),transactionVO.getTransactionDate(),transactionVO.getTotalPrice(), transactionVO.getTransaction_type()};
		insert(sql, params);
		
	}

	@Override
	public double getMoneyStatistic(String role, int publisherId,
			String fromDate, String toDate) {
		StringBuilder sql = new StringBuilder();
		sql.append("Select sum(t.total_price) ");
		sql.append("FROM transaction t, book b, publisher p ");
		sql.append("WHERE t.book_id = b.id ");
		sql.append("AND b.publisher_id = p.id ");
		sql.append("AND b.publisher_id = " + publisherId + " ");
		sql.append("AND DATE(transaction_date) ");
		if(toDate != null) {
			sql.append(" between ").append("DATE('").append(fromDate).append("')")
			.append(" and ").append("DATE('").append(toDate).append("')");
		} else {
			sql.append(" = DATE('").append(fromDate).append("')");
		}
		Double result = queryForDouble(sql.toString());
		if(result == null) return 0;
		return result;
	}
}
